﻿--LinkBook-----------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LinkBook]'))
DROP TABLE [LinkBook]
GO
CREATE TABLE [LinkBook](
	[Key] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[Title] [nvarchar](MAX) NOT NULL,
	[IsFolder] [bit] NOT NULL DEFAULT 0,
	[ParentFolder] [uniqueidentifier] NULL,
	[Url] [nvarchar](MAX) NOT NULL,
	[Domain] [nvarchar](MAX) NOT NULL,
	[IsDeleted] [bit] NOT NULL DEFAULT 0,
	[CreatedStamp] [datetime] NOT NULL,
	[LastUpdatedStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_LinkBook] PRIMARY KEY CLUSTERED ([Key] ASC)) ON [PRIMARY]
GO
----- sp_QueryLink -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryLink]'))
DROP PROCEDURE [dbo].[sp_QueryLink]
GO
CREATE PROCEDURE [sp_QueryLink](
	@Keyword [nvarchar](MAX),
	@IncludeDeleted bit
)
AS
BEGIN
	DECLARE @SqlExp AS nvarchar(Max);
	DECLARE @WhereExp AS nvarchar(Max);

	SET @SqlExp = 'SELECT [Key],[Title],[IsFolder],[ParentFolder],[Url],[Domain],[IsDeleted],[CreatedStamp],[LastUpdatedStamp] FROM [dbo].[LinkBook]';
	SET @WhereExp = ' WHERE [IsFolder] = 0';

	IF(@Keyword IS NOT NULL AND @Keyword <> '')
		BEGIN
		SET @Keyword = ReformatLikeStatement(@Keyword);
		SET @WhereExp = @WhereExp +  ' AND ([Title] LIKE ''%' +@Keyword + '%'' OR [Url] LIKE ''%' + @Keyword  + '%'')';
		END
	IF(@IncludeDeleted IS NULL OR @IncludeDeleted = 0)
	BEGIN
		SET @WhereExp = @WhereExp +  ' AND [IsDeleted] = 0';
	END

	SET @SqlExp = @SqlExp + @WhereExp + ' ORDER BY [IsFolder] DESC, [Title]';
	exec (@SqlExp);
END
GO
----- sp_AddLink -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_AddLink]'))
DROP PROCEDURE [dbo].[sp_AddLink]
GO
CREATE PROCEDURE [sp_AddLink](
	@Keyword [nvarchar](MAX),
	@IncludeDeleted bit
)
AS
BEGIN
	DECLARE @SqlExp AS nvarchar(Max);
	DECLARE @WhereExp AS nvarchar(Max);

	SET @SqlExp = 'SELECT [Key],[Title],[Url],[Domain],[IsDeleted],[CreatedStamp],[LastUpdatedStamp] FROM [dbo].[LinkBook]';
	SET @WhereExp = '';

	IF(@Keyword IS NOT NULL AND @Keyword <> '')
		SET @WhereExp = @WhereExp +  ' AND (([Type] & ' + CONVERT(NVARCHAR(20), @Type) + ') > 0)';
	IF(@Keyword IS NOT NULL)
	BEGIN
		SET @Keyword = dbo.ReformatLikeStatement(@Keyword);		
		IF(@Keyword <> '')
			SET @WhereExp = @WhereExp +  ' AND ([Name] LIKE ''%' + @Keyword + '%'' OR [Code] LIKE ''%' + @Keyword + '%'')';
	END

	SET @SqlExp = @SqlExp + @WhereExp;
	exec (@SqlExp);
END
GO